﻿CREATE PROCEDURE [dbo].[proc_Project_Task_Getlist_Dep]
	(
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Stext nvarchar(50),
		@CompanyId int,
		@DepId int,
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY IsFinish,CreateDate Desc,Id Desc)AS Row
		,Id
		,TaskId
		,OrderId
		,ProjectId
		,ProductId
		,CusName
		,Title
		,Description
		,DownPersonName
		,StartDate
		,ExecDepartmentName
		,IsNull(EndDate,'1900-01-01') As EndDate
		,IsNull(EndDate,GetDate()) As EndDate2
		From Project_Task
		Where ExecDepartmentId=@DepId And TypeId=1
		And CreateDate>=@sRq And CreateDate<=@eRq
		And (@Stext='' Or CusName like '%'+@Stext+'%')
		And execperson=''	--2012-03-27
	)

	Select *,
	DateDiff(day,StartDate,EndDate2) As CountDay,
	(Case When (Select Count(0) From Project_Task p Where p.ProjectId=list.ProjectId And p.IsFinish=0 And p.execperson='' And p.ExecDepartmentId=@DepId)=0 Then 1 Else 0 End) As IsFinish,
	(Select Count(0) From Project_Task p Where p.ProjectId=list.ProjectId And p.ExecDepartmentId=@DepId And p.ExecPerson='') As ChildCount,
	(Select Count(0) From List) As RecordCount
	From list
	Where Row between @startIndex and @EndIndex
	Order By Row
End
